import os
from fastapi import APIRouter
from util.common import select_file, process_and_create_email
from routes import sqlite, base_path, recipient_email, MaximumAttachmentSize, pdf_path, config_df
import pandas as pd
# import win32com.client as win32
import polars as pl

email_app = APIRouter()


# 生成邮件
# def create_email_template(recipient_email, bf_html, mailAttachments, MaximumAttachmentSize):
#     outlook = win32.Dispatch('Outlook.Application')
#     # 创建一封邮件
#     mail = outlook.CreateItem(0)
#
#     # 邮件收件人
#     mail.To = recipient_email if recipient_email else ''
#
#     # 获取当前邮件的签名（如果有）
#     mail.Display()  # 显示邮件窗口，Outlook会自动加载签名
#     signature = mail.HTMLBody  # 获取包含签名的当前HTMLBody
#
#     head = """
#         <head>
#         <style>
#             body {
#                 font-size: 14px;     /* 设置字体大小为5号（14px） */
#                 font-family: 'SimSun', 'Microsoft YaHei', sans-serif;  /* 优先使用宋体 */
#                 mso-font-alt: 'SimSun'; /* 强制指定宋体 */
#             }
#             table {
#                     border-collapse: collapse;
#                 }
#             th, td {
#                 text-align: center;  /* 将文本居中 */
#                 padding: 8px;        /* 添加一些内边距以改善可读性 */
#
#             }
#             th {
#                 background: #86A8E7;
#             }
#             td {
#                 line-height: 7px;    /* 设置行高为10像素 */
#                 height: 7px;         /* 设置单元格高度为10像素 */
#             }
#         </style>
#         </head>
#         """
#     custom_body = f"""
#     <html>
#     {head}
#     <body>
#     {bf_html}
#     </body>
#     </html>
#     """
#     # 将自定义内容添加到签名前
#     mail.HTMLBody = custom_body + signature
#
#     MAX_ATTACHMENT_SIZE = MaximumAttachmentSize * 1024 * 1024  # 转换为字节
#     total_size = 0
#
#     # 处理附件
#     for attachment in mailAttachments:
#         if attachment['is_file']:
#             # 如果是文件路径
#             file_size = os.path.getsize(attachment['path'])
#             if total_size + file_size > MAX_ATTACHMENT_SIZE:
#                 mail.Subject = f"文件大小超过{MaximumAttachmentSize}MB，无法添加附件"
#                 print(f"文件大小超过{MaximumAttachmentSize}MB，无法添加附件")
#                 break
#             total_size += file_size
#             mail.Attachments.Add(str(attachment['path']))
#         else:
#             # 如果是内存中的数据
#             data_size = len(attachment['data'].getvalue())
#             if total_size + data_size > MAX_ATTACHMENT_SIZE:
#                 mail.Subject = f"文件大小超过{MaximumAttachmentSize}MB，无法添加附件"
#                 print(f"文件大小超过{MaximumAttachmentSize}MB，无法添加附件")
#                 break
#
#             # 创建临时文件并写入数据
#             temp_path = os.path.join(os.path.dirname(os.path.realpath(__file__)), 'temp_attachments')
#             os.makedirs(temp_path, exist_ok=True)
#             temp_file = os.path.join(temp_path, attachment['name'])
#
#             with open(temp_file, 'wb') as f:
#                 f.write(attachment['data'].getvalue())
#
#             total_size += data_size
#             mail.Attachments.Add(str(temp_file))
#             # 添加后立即删除临时文件
#             os.remove(temp_file)
#
#     print(f"附件总大小：{total_size / 1024 / 1024}MB")
#
#     # 显示邮件前预览
#     mail.Display()


@email_app.get("/createEmail")
async def read_excel(id: int = 1):
    try:
        if pdf_path == '':
            return {
                "code": 2001,
                "status": "no",
                "message": "没有设置pdf文件存放路径，请检查配置文件",
                "data": []
            }

        excelpath = select_file('请选择要生成邮件的excel文件')
        if excelpath == '':
            return {
                "code": 2002,
                "status": "no",
                "message": "没有选择文件",
                "data": []
            }
        df = pd.DataFrame()
        box = ''
        con = ''
        item = ''
        sub_item = ''
        if id == 1:
            df1 = config_df['模板1']
            box = df.filter(pl.col("原字段名") == "箱单号")["现字段名"].item(0)
            con = df.filter(pl.col("原字段名") == "合同号")["现字段名"].item(0)
            item = df.filter(pl.col("原字段名") == "危险品货物明细描述")["现字段名"].item(0)
            sub_item = df.filter(pl.col("原字段名") == "子项危险品信息")["现字段名"].item(0)
            # 读取excel
            df = pd.read_excel(excelpath, sheet_name=df1["表名"].head(1).item(),
                               usecols=df1["现字段名"].to_list(),
                               dtype={item: str, sub_item: str})
            df.rename(columns={box: box, con: con, item: item, sub_item: sub_item}, inplace=True)
        elif id == 2:
            df2 = config_df['模板2']
            con = df2.filter(pl.col("原字段名") == "合同号")["现字段名"].item(0)
            item = df2.filter(pl.col("原字段名") == "物料编码")["现字段名"].item(0)
            df = pd.read_excel(excelpath, sheet_name=df2["表名"].head(1).item(),
                               usecols=df2["现字段名"].to_list(),
                               dtype={item: str})
            df.rename(columns={con: con, item: item}, inplace=True)
            df[box] = ''
            df[sub_item] = ''
        if df.empty:
            return {
                "code": 2003,
                "status": "no",
                "message": "获取数据为空",
                "data": []
            }
        # 数据库中查询Certificate Code
        df_items = df[item].drop_duplicates().values
        # 将 item 列的值连接成一个字符串
        result = ", ".join(f"'{x}'" for x in df_items)
        # sql = f"SELECT Item,CertificateCode FROM CertificateCode WHERE Item IN({result})"
        sql = f"""
            SELECT prio, Item, CertificateCode, CertifiedModel, battCode
            FROM CertificateCode
            WHERE (Item, prio) IN (
                SELECT Item, MIN(prio)
                FROM CertificateCode
                WHERE Item IN ({result})
                GROUP BY Item
            )
        """
        df_res = sqlite.query(sql)
        # 将 CertificateCode 按照 Item 进行分组，并用逗号连接
        result = df_res.groupby('Item')['CertificateCode'].agg(','.join).reset_index()
        df = df.merge(result, left_on=item, right_on='Item', how='left')
        df.fillna('', inplace=True)
        tr = ''
        for index, r in df.iterrows():
            tr += f"""
                <tr>
                <td>{r[box]}</td>
                <td>{r[con]}</td>
                <td>{r[item]}</td>
                <td>{r[sub_item]}</td>
                <td>{r['CertificateCode']}</td>
            </tr>
                """
        # html模板
        df_html = f"""
                <table  border=1>
                    <thead>
                        <tr>
                            <th>箱单号</th>
                            <th>合同号</th>
                            <th>危险品货物明细描述</th>
                            <th>子项危险品信息</th>
                            <th>CertificateCode</th>
                        </tr>
                    </thead>
                    <tbody>
                       {tr}
                    </tbody>
                </table>
                """
        # 获取pdf文件并打包
        # pdfzip = os.path.join(base_path, 'pdf_zip')
        # create_folder_if_not_exists(pdfzip)
        resdf = result.drop_duplicates('Item')
        res = process_and_create_email(resdf, pdf_path, recipient_email, df_html, MaximumAttachmentSize)
        return res

    except Exception as e:
        return {
            "code": 500,
            "status": "error",
            "message": str(e),
            "data": []
        }


@email_app.get("/create_oneEmail")
async def create_oneEmail(id: str):
    try:
        data_sql = f"SELECT Item, CertificateCode FROM CertificateCode WHERE id in ({id})"
        df = sqlite.query(data_sql)
        if df.empty:
            return {
                "code": 2001,
                "status": "no",
                "message": "没有找到匹配的记录",
                "data": []
            }
        df.rename(columns={'Item': '危险品货物明细描述'}, inplace=True)

        if pdf_path == '':
            return {
                "code": 2001,
                "status": "no",
                "message": "没有设置pdf文件存放路径，请检查配置文件",
                "data": []
            }

        # 将 CertificateCode 按照 Item 进行分组，并用逗号连接
        df = df.groupby('危险品货物明细描述')['CertificateCode'].agg(','.join).reset_index()
        df['箱单号'] = ''
        df['合同号'] = ''
        df['子项危险品信息'] = ''
        tr = ''
        for index, r in df.iterrows():
            tr += f"""
                <tr>
                <td>{r['箱单号']}</td>
                <td>{r['合同号']}</td>
                <td>{r['危险品货物明细描述']}</td>
                <td>{r['子项危险品信息']}</td>
                <td>{r['CertificateCode']}</td>
            </tr>
                """
        # html模板
        df_html = f"""
                <table  border=1>
                    <thead>
                        <tr>
                            <th>箱单号</th>
                            <th>合同号</th>
                            <th>危险品货物明细描述</th>
                            <th>子项危险品信息</th>
                            <th>CertificateCode</th>
                        </tr>
                    </thead>
                    <tbody>
                       {tr}
                    </tbody>
                </table>
                """
        # 获取pdf文件并打包
        df.rename(columns={'危险品货物明细描述': 'Item'}, inplace=True)
        # pdfzip = os.path.join(base_path, 'pdf_zip')
        # create_folder_if_not_exists(pdfzip)
        res = process_and_create_email(df, pdf_path, recipient_email, df_html, MaximumAttachmentSize)
        return res

    except Exception as e:
        return {
            "code": 500,
            "status": "error",
            "message": str(e),
            "data": []
        }
